﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace Shrestha.Trigraph
{
    public class MySqlDbManager : DbManager
    {
        private MySqlConnection myConn;
        
        public bool InitDb(string host, string database, string user, string password)
        {
            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            try
            {
                myConn = new MySqlConnection(strProvider);
                myConn.Open();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }
        }

        public List<TriEvent> GetTriEvents(string queryText)
        {
            List<TriEvent> teList = new List<TriEvent>();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            MySqlDataReader mdr = cmd.ExecuteReader();
            while (mdr.Read())
            {
                try
                {
                    TriEvent te = new TriEvent(Double.Parse(mdr.GetDouble(0).ToString()), Double.Parse(mdr.GetDouble(1).ToString()), DateTime.Parse(mdr.GetDateTime(2).ToString()));
                    teList.Add(te);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            return teList;
        }

        public object ExecuteQuery(string queryText)
        {
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            return cmd.ExecuteScalar();
        }

        public WorkingSet ExtractParameters(string tableName, string latName, string lngName, string timeName)
        {
            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

    }
}
